const fs = require('fs');
const path = require('path');

class DatabaseLayer {
    constructor() {
        this.db = null;
        this.init();
    }

    async init () {
        try {
            // 动态导入sql.js
            const initSqlJs = require('sql.js');
            const SQL = await initSqlJs();

            // 读取现有的数据库文件
            const dbBuffer = fs.readFileSync('db.sqlite3');
            this.db = new SQL.Database(dbBuffer);

            console.log('成功连接到SQLite数据库');
            await this.createTables();
        } catch (err) {
            console.error('数据库初始化失败:', err.message);
            // 如果读取现有数据库失败，创建新的数据库
            try {
                const initSqlJs = require('sql.js');
                const SQL = await initSqlJs();
                this.db = new SQL.Database();
                console.log('创建新的SQLite数据库');
                await this.createTables();
            } catch (error) {
                console.error('创建新数据库失败:', error.message);
            }
        }
    }

    async createTables () {
        // 检查表是否已存在
        const tables = this.db.exec(`
            SELECT name FROM sqlite_master 
            WHERE type='table' AND name='videos'
        `);

        if (tables.length === 0) {
            // 创建视频主表
            const videosTable = `
                CREATE TABLE videos (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    filename TEXT UNIQUE NOT NULL,
                    filepath TEXT NOT NULL,
                    filesize INTEGER NOT NULL,
                    filetype TEXT NOT NULL,
                    title TEXT NOT NULL,
                    author TEXT DEFAULT '',
                    series TEXT DEFAULT '',
                    tags TEXT DEFAULT '',
                    rating INTEGER DEFAULT 0,
                    view_count INTEGER DEFAULT 0,
                    description TEXT DEFAULT '',
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `;

            // 创建标签字典表
            const tagsTable = `
                CREATE TABLE tags (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT UNIQUE NOT NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            `;

            // 创建视频标签关联表
            const videoTagsTable = `
                CREATE TABLE video_tags (
                    video_id INTEGER,
                    tag_id INTEGER,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    PRIMARY KEY (video_id, tag_id),
                    FOREIGN KEY (video_id) REFERENCES videos (id) ON DELETE CASCADE,
                    FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
                )
            `;

            try {
                this.db.run(videosTable);
                this.db.run(tagsTable);
                this.db.run(videoTagsTable);
                console.log('数据库表创建成功');
                this.saveDatabase();
            } catch (err) {
                console.error('创建数据库表失败:', err.message);
            }
        } else {
            console.log('数据库表已存在');
        }
    }

    // 保存数据库到文件
    saveDatabase () {
        try {
            const data = this.db.export();
            const buffer = Buffer.from(data);
            fs.writeFileSync('db.sqlite3', buffer);
        } catch (err) {
            console.error('保存数据库失败:', err.message);
        }
    }

    // 扫描并导入视频文件
    scanVideos (videoDir, callback) {
        const supportedFormats = ['.mp4', '.avi', '.mkv', '.mov', '.wmv', '.flv', '.webm'];

        const scanDirectory = (dir, files = []) => {
            try {
                const items = fs.readdirSync(dir, { withFileTypes: true });

                for (const item of items) {
                    const fullPath = path.join(dir, item.name);

                    if (item.isDirectory()) {
                        // 递归扫描子目录
                        scanDirectory(fullPath, files);
                    } else if (item.isFile()) {
                        const ext = path.extname(item.name).toLowerCase();
                        if (supportedFormats.includes(ext)) {
                            const stats = fs.statSync(fullPath);
                            files.push({
                                filename: item.name,
                                filepath: fullPath,
                                filesize: stats.size,
                                filetype: ext,
                                title: path.parse(item.name).name,
                                created_at: new Date(stats.birthtime).toISOString()
                            });
                        }
                    }
                }
            } catch (error) {
                console.error(`扫描目录错误 ${dir}:`, error.message);
            }

            return files;
        };

        try {
            const videoFiles = scanDirectory(videoDir);
            this.importVideos(videoFiles, callback);
        } catch (error) {
            callback({ success: false, error: error.message });
        }
    }

    // 导入视频到数据库
    importVideos (videoFiles, callback) {
        try {
            const stmt = this.db.prepare(`
                INSERT OR IGNORE INTO videos 
                (filename, filepath, filesize, filetype, title, created_at) 
                VALUES (?, ?, ?, ?, ?, ?)
            `);

            let imported = 0;
            let skipped = 0;

            for (const video of videoFiles) {
                try {
                    stmt.run([
                        video.filename,
                        video.filepath,
                        video.filesize,
                        video.filetype,
                        video.title,
                        video.created_at
                    ]);
                    if (this.db.getRowsModified() > 0) {
                        imported++;
                    } else {
                        skipped++;
                    }
                } catch (err) {
                    console.error('导入视频失败:', err.message);
                    skipped++;
                }
            }

            this.saveDatabase();

            callback({
                success: true,
                imported,
                skipped,
                total: videoFiles.length
            });
        } catch (err) {
            callback({ success: false, error: err.message });
        }
    }

    // 获取视频列表
    getVideos (params = {}, callback) {
        try {
            let sql = `
                SELECT id, filename, filepath, filesize, filetype, title, author, 
                       series, tags, rating, view_count, description, created_at, updated_at
                FROM videos 
                WHERE 1=1
            `;
            const conditions = [];
            const values = [];

            // 搜索条件
            if (params.search) {
                conditions.push(`(title LIKE ? OR author LIKE ? OR series LIKE ? OR tags LIKE ?)`);
                const searchTerm = `%${params.search}%`;
                values.push(searchTerm, searchTerm, searchTerm, searchTerm);
            }

            // 标签筛选
            if (params.tag) {
                conditions.push(`tags LIKE ?`);
                values.push(`%${params.tag}%`);
            }

            // 系列筛选
            if (params.series) {
                conditions.push(`series = ?`);
                values.push(params.series);
            }

            if (conditions.length > 0) {
                sql += ' AND ' + conditions.join(' AND ');
            }

            // 排序
            sql += ' ORDER BY updated_at DESC';

            // 分页
            if (params.limit) {
                sql += ' LIMIT ?';
                values.push(params.limit);
            }

            if (params.offset) {
                sql += ' OFFSET ?';
                values.push(params.offset);
            }

            const stmt = this.db.prepare(sql);
            stmt.bind(values);
            const rows = [];
            while (stmt.step()) {
                rows.push(stmt.getAsObject());
            }
            stmt.free();

            callback(null, rows);
        } catch (err) {
            callback(err, null);
        }
    }

    // 获取单个视频详情
    getVideoById (id, callback) {
        try {
            const sql = `
                SELECT id, filename, filepath, filesize, filetype, title, author, 
                       series, tags, rating, view_count, description, created_at, updated_at
                FROM videos 
                WHERE id = ?
            `;
            const stmt = this.db.prepare(sql);
            stmt.bind([id]);
            const row = stmt.step() ? stmt.getAsObject() : null;
            stmt.free();

            callback(null, row);
        } catch (err) {
            callback(err, null);
        }
    }

    // 更新视频元数据
    updateVideo (id, updates, callback) {
        try {
            const fields = [];
            const values = [];

            Object.keys(updates).forEach(key => {
                if (key !== 'id') {
                    fields.push(`${key} = ?`);
                    values.push(updates[key]);
                }
            });

            fields.push('updated_at = CURRENT_TIMESTAMP');
            values.push(id);

            const sql = `UPDATE videos SET ${fields.join(', ')} WHERE id = ?`;
            const stmt = this.db.prepare(sql);
            stmt.run(values);
            this.saveDatabase();

            callback({
                success: true,
                changes: this.db.getRowsModified()
            });
        } catch (err) {
            callback({ success: false, error: err.message });
        }
    }

    // 增加播放次数
    incrementViewCount (id, callback) {
        try {
            const sql = `UPDATE videos SET view_count = view_count + 1 WHERE id = ?`;
            const stmt = this.db.prepare(sql);
            stmt.run([id]);
            this.saveDatabase();
            callback(null, { changes: this.db.getRowsModified() });
        } catch (err) {
            callback(err, null);
        }
    }

    // 获取所有标签
    getAllTags (callback) {
        try {
            const sql = `
                SELECT DISTINCT tags 
                FROM videos 
                WHERE tags IS NOT NULL AND tags != ''
            `;
            const stmt = this.db.prepare(sql);
            const rows = [];
            while (stmt.step()) {
                rows.push(stmt.getAsObject());
            }
            stmt.free();

            const allTags = new Set();
            rows.forEach(row => {
                if (row.tags) {
                    row.tags.split(',').forEach(tag => {
                        if (tag.trim()) {
                            allTags.add(tag.trim());
                        }
                    });
                }
            });
            callback(null, Array.from(allTags).sort());
        } catch (err) {
            callback(err, null);
        }
    }

    // 获取所有系列
    getAllSeries (callback) {
        try {
            const sql = `
                SELECT DISTINCT series 
                FROM videos 
                WHERE series IS NOT NULL AND series != ''
                ORDER BY series
            `;
            const stmt = this.db.prepare(sql);
            const rows = [];
            while (stmt.step()) {
                rows.push(stmt.getAsObject());
            }
            stmt.free();
            callback(null, rows);
        } catch (err) {
            callback(err, null);
        }
    }

    // 关闭数据库连接
    close () {
        if (this.db) {
            this.db.close();
            console.log('数据库连接已关闭');
        }
    }
}

module.exports = DatabaseLayer;
